﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SampleApplication
{
    using System.Runtime.InteropServices;
    using System.Data.OleDb;
    public static class OdbcWrapper
    {
        [DllImport("odbc32.dll")]
        public static extern int SQLDataSources(int EnvHandle, int Direction, StringBuilder ServerName, int ServerNameBufferLenIn,
    ref int ServerNameBufferLenOut, StringBuilder Driver, int DriverBufferLenIn, ref int DriverBufferLenOut);

        [DllImport("odbc32.dll")]
        public static extern int SQLAllocEnv(ref int EnvHandle);
    }

    public partial class DisplayProviders : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                using (DataTable providers = DbProviderFactories.GetFactoryClasses())
                {
                    Providers.DataSource = providers;
                    Providers.DataBind();
                }

                OdbcDsns.DataSource = ListOdbcDsn();
                OdbcDsns.DataBind();

                OdbcProviders.DataSource = GetSystemDriverList();
                OdbcProviders.DataBind();

                OledbProviders.DataSource = new OleDbEnumerator().GetElements();
                 OledbProviders.DataBind();
            }
        }

        public class OdbcSource
        {
            public string ServerName { get; set; }
            public string Driver { get; set; }
        }


        //Ref http://stackoverflow.com/questions/6457973/odbc-driver-list-from-net
        public static List<String> GetSystemDriverList()
               {
            List<string> names = new List<string>();
            // get system dsn's
            Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC");
                if (reg != null)
                {
                    reg = reg.OpenSubKey("ODBCINST.INI");
                    if (reg != null)
                    {

                        reg = reg.OpenSubKey("ODBC Drivers");
                        if (reg != null)
                        {
                            // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                            foreach (string sName in reg.GetValueNames())
                            {
                                names.Add(sName);
                            }
                        }
                        try
                        {
                            reg.Close();
                        }
                        catch { /* ignore this exception if we couldn't close */ }
                    }
                }
            }

            return names;
        }

        //Code Ref http://stackoverflow.com/questions/562016/listing-odbc-data-sources-in-c-sharp
        public List<OdbcSource> ListODBCsources()
        {
            int envHandle = 0;
            const int SQL_FETCH_NEXT = 1;
            const int SQL_FETCH_FIRST_SYSTEM = 32;

            List<OdbcSource> sources = new List<OdbcSource>();
            if (OdbcWrapper.SQLAllocEnv(ref envHandle) != -1)
            {
                int ret;
                StringBuilder serverName = new StringBuilder(1024);
                StringBuilder driverName = new StringBuilder(1024);
                int snLen = 0;
                int driverLen = 0;
                
                //Just system 
                    ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_FIRST_SYSTEM, serverName, serverName.Capacity, ref snLen,
                            driverName, driverName.Capacity, ref driverLen);
                while (ret == 0)
                {
                    //System.Windows.Forms.MessageBox.Show(serverName + System.Environment.NewLine + driverName);
                    sources.Add(new OdbcSource() { ServerName = serverName.ToString(), Driver = driverName.ToString() });
                    
                    ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_NEXT, serverName, serverName.Capacity, ref snLen,
                            driverName, driverName.Capacity, ref driverLen);
                }
                
            }
            return sources;
        }

        //User and system.
        public List<OdbcSource> ListOdbcDsn()
        {
            int envHandle = 0;
            const int SQL_FETCH_NEXT = 1;
            //const int SQL_FETCH_FIRST_SYSTEM = 32;

            List<OdbcSource> sources = new List<OdbcSource>();
            if (OdbcWrapper.SQLAllocEnv(ref envHandle) != -1)
            {
                int ret;
                StringBuilder serverName = new StringBuilder(1024);
                StringBuilder driverName = new StringBuilder(1024);
                int snLen = 0;
                int driverLen = 0;


                ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_NEXT, serverName, serverName.Capacity, ref snLen,
                        driverName, driverName.Capacity, ref driverLen);
                while (ret == 0)
                {
                    //System.Windows.Forms.MessageBox.Show(serverName + System.Environment.NewLine + driverName);
                    sources.Add(new OdbcSource() { ServerName = serverName.ToString(), Driver = driverName.ToString() });

                    ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_NEXT, serverName, serverName.Capacity, ref snLen,
                            driverName, driverName.Capacity, ref driverLen);
                }

            }
            return sources;
        }
    }
}